﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;

namespace Transcend.DP3.Db
{

	public class AbstractDatabase
	{

		protected static readonly System.Text.RegularExpressions.Regex ParamPlaceholderPattern = new System.Text.RegularExpressions.Regex("\\:[^\\,\\)';\\s$\"\\=]+");

		/// <summary>
		/// 使用连接字符串，创建并打开一个新数据库连接实例
		/// </summary>
		public static DbConnection mkConnection(string connString)
		{
			var builder = new System.Data.Common.DbConnectionStringBuilder();
			builder.ConnectionString = connString;
			var provider = string.Empty;
			foreach (string key in builder.Keys)
			{
				if ("Provider".Equals(key, StringComparison.OrdinalIgnoreCase))
				{
					provider = builder[key] as string;
					builder.Remove(key);
					break;
				}
			}

			if (string.IsNullOrEmpty(provider))
			{
				throw new ArgumentException(string.Format("连接字符串{0}没有指定provider属性", connString));
			}

			var fact = System.Data.Common.DbProviderFactories.GetFactory(provider);
			var conn = fact.CreateConnection();
			conn.ConnectionString = builder.ConnectionString;
			conn.Open();
			return conn;
		}

		/// <summary>
		/// 解析出SQL语句中的参数占位符号，占位符的格式必须满足正则\:[^\,\)';\s$\"]+
		/// </summary>
		public static List<string> ParseParamPlaceholder(string sql)
		{
			var holder = new List<string>();
			var mc = ParamPlaceholderPattern.Matches(sql);
			foreach (System.Text.RegularExpressions.Match m in mc)
			{
				holder.Add(m.Value.Substring(1));
			}
			return holder;
		}

		/// <summary>
		/// 通过参数名称取得参数值
		/// </summary>
		protected static object GetParamVal(Dictionary<string, object> paramConext, string pName)
		{
			object val = DBNull.Value;
			if (paramConext == null || !paramConext.ContainsKey(pName))
			{
				throw new ArgumentException(string.Format("没有找到参数占位{0}所对应的参数值", pName));
			}
			val = paramConext[pName];
			if (val == null) val = DBNull.Value;
			return val;
		}

		/// <summary>
		/// 创建参数并加入到DbCommand中
		/// </summary>
		public static DbParameter mkParam(DbCommand cmd, string name, object val)
		{
			var p = cmd.CreateParameter();
			p.ParameterName = name;
			p.Value = val;
			cmd.Parameters.Add(p);
			return p;
		}

		/// <summary>
		/// 将reader当前行转换成Dictionary
		/// </summary>
		public static Dictionary<string, object> RowToDic(System.Data.Common.DbDataReader dr)
		{
			var dic = new Dictionary<string, object>(dr.FieldCount);
			for (int i = 0; i < dr.FieldCount; i++)
			{
				dic[dr.GetName(i)] = NormalizeDbVal(dr[i]);
			}
			return dic;
		}

		/// <summary>
		/// 处理数据库返回值
		/// </summary>
		/// <param name="dbVal"></param>
		/// <returns></returns>
		public static object NormalizeDbVal(object dbVal)
		{
			return dbVal == DBNull.Value ? null : dbVal;
		}

		public static object MapRow(System.Data.Common.DbDataReader dr, Type t)
		{
			object ret = null;
			System.Reflection.PropertyInfo prop = null;
			if (dr.Read())
			{
				ret = Activator.CreateInstance(t);
				for (int i = 0; i < dr.FieldCount; i++)
				{
					prop = PropName(t, dr.GetName(i));
					if (prop != null) prop.SetValue(ret, PropVal(dr[i], prop.PropertyType), null);
				}
			}
			return ret;
		}

		/// <summary>
		/// 将DataReader中的第一行结果映射到类型T的实例
		/// </summary>
		public static T MapRow<T>(System.Data.Common.DbDataReader dr)
		{
			T ret = default(T);
			Type t = typeof(T);
			System.Reflection.PropertyInfo prop = null;
			if (dr.Read())
			{
				ret = Activator.CreateInstance<T>();
				for (int i = 0; i < dr.FieldCount; i++)
				{
					prop = PropName(t, dr.GetName(i));
					if (prop != null) prop.SetValue(ret, PropVal(dr[i], prop.PropertyType), null);
				}
				return ret;
			}

			return ret;
		}

		/// <summary>
		/// 取得dr中某一个字段的值
		/// </summary>
		public static T GetValue<T>(System.Data.Common.DbDataReader dr, string field, T defValue = default(T))
		{
			var val = NormalizeDbVal(dr[field]);
			if (val == null) return defValue;
			return (T)Convert.ChangeType(val, typeof(T));
		}

		/// <summary>
		/// 取得dr中某一个字段的值
		/// </summary>
		public static T GetValue<T>(System.Data.Common.DbDataReader dr, int ord, T defValue = default(T))
		{
			var val = NormalizeDbVal(dr[ord]);
			if (val == null) return defValue;
			return (T)Convert.ChangeType(val, typeof(T));
		}

		/// <summary>
		/// 将Dbcommand转换成直接可执行的语句
		/// </summary>
		public static string DumpCommand(DbCommand cmd)
		{
			if (string.IsNullOrEmpty(cmd.CommandText)) return "无执行的语句";

			var cmdText = cmd.CommandText;
			var param = ParseParamPlaceholder(cmdText);
			var pCnt = cmd.Parameters.Count;
			DbParameter p = null;

			var mStart = 0;
			var matches = ParamPlaceholderPattern.Matches(cmdText);
			var sVal = "null";
			System.Text.RegularExpressions.Match m = null;

			if (matches.Count == 0)
			{
				return cmdText;
			}

			var buf = new StringBuilder(cmdText.Length);
			for (var i = 0; i < matches.Count; i++)
			{
				// 实际解析出来的参数值多余传入的参数值
				if (i >= pCnt) break;

				m = matches[i];
				buf.Append(cmdText.Substring(mStart, m.Index - mStart));
				p = cmd.Parameters[i];

				if (p.Value != null)
				{
					switch (Type.GetTypeCode(p.Value.GetType()))
					{
						case TypeCode.Byte:
						case TypeCode.Char:
						case TypeCode.Decimal:
						case TypeCode.Double:
						case TypeCode.Int16:
						case TypeCode.Int32:
						case TypeCode.Int64:
						case TypeCode.Object:
						case TypeCode.SByte:
						case TypeCode.Single:
						case TypeCode.UInt16:
						case TypeCode.UInt32:
						case TypeCode.UInt64:
							sVal = Convert.ToString(p.Value);
							break;
						case TypeCode.Boolean:
							sVal = Convert.ToString(p.Value);
							break;
						case TypeCode.String:
							sVal = p.Value as string;
							if (sVal.Length > 100)
							{
								sVal = sVal.Substring(0, 100);
								sVal = "......省略";
							}
							sVal = string.Format("'{0}'", sVal);
							break;
						case TypeCode.DateTime:
							sVal = string.Format("'{0}'", p.Value);
							break;
						case TypeCode.Empty:
						case TypeCode.DBNull:
							sVal = "null";
							break;
						default:
							sVal = string.Format("'{0}'", p.Value.ToString());
							break;
					}
				}
				else
				{
					sVal = "null";
				}
				buf.Append(sVal);
				mStart = m.Index + m.Value.Length;
			}

			if (mStart > 0)
				buf.Append(cmdText.Substring(mStart));
			return buf.ToString();
		}

		/// <summary>
		/// 将数据库值转换成目标属性数据类型
		/// </summary>
		protected static object PropVal(object value, Type propType)
		{
			if (propType.IsGenericType && propType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
			{
				if (value == null)
					return null;
				var nullableConverter = new System.ComponentModel.NullableConverter(propType);
				propType = nullableConverter.UnderlyingType;
			}
			return Convert.ChangeType(value, propType);
		}

		/// <summary>
		/// 将数据库列名转换成实体属性名称
		/// </summary>
		protected static System.Reflection.PropertyInfo PropName(Type t, string column)
		{
			column = column.ToLower();
			string[] narr = column.Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries);
			column = "";
			for (int i = 0; i < narr.Length; i++)
			{
				if (narr[i].Length > 1)
				{
					column += narr[i].Substring(0, 1).ToUpper() + narr[i].Substring(1);
				}
				else
				{
					column += narr[i].Substring(0, 1).ToUpper();
				}
			}

			var props = t.GetProperties();
			if (props != null)
			{
				foreach (var p in props)
				{
					if (column.Equals(p.Name, StringComparison.OrdinalIgnoreCase))
					{
						return p;
					}
				}
			}

			return null;
		}
	}

}
